package com.example.repository;

import com.example.model.Expense;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface ExpenseRepository extends JpaRepository<Expense, Long> {
    
    // 根据类型查找支出
    Page<Expense> findByType(Expense.ExpenseType type, Pageable pageable);
    
    // 根据分类查找支出
    Page<Expense> findByCategory(Expense.ExpenseCategory category, Pageable pageable);
    
    // 根据审批状态查找支出
    Page<Expense> findByApprovalStatus(Expense.ApprovalStatus status, Pageable pageable);
    
    // 获取总支出
    @Query("SELECT COALESCE(SUM(e.amount), 0) FROM Expense e WHERE e.approvalStatus = 'APPROVED'")
    BigDecimal getTotalExpense();
    
    // 获取今日支出
    @Query("SELECT COALESCE(SUM(e.amount), 0) FROM Expense e WHERE e.expenseDate >= :startOfDay AND e.expenseDate < :endOfDay AND e.approvalStatus = 'APPROVED'")
    BigDecimal getTodayExpense(@Param("startOfDay") LocalDateTime startOfDay, @Param("endOfDay") LocalDateTime endOfDay);
    
    // 获取本月支出
    @Query("SELECT COALESCE(SUM(e.amount), 0) FROM Expense e WHERE YEAR(e.expenseDate) = YEAR(CURRENT_DATE) AND MONTH(e.expenseDate) = MONTH(CURRENT_DATE) AND e.approvalStatus = 'APPROVED'")
    BigDecimal getMonthlyExpense();
    
    // 获取本年支出
    @Query("SELECT COALESCE(SUM(e.amount), 0) FROM Expense e WHERE YEAR(e.expenseDate) = YEAR(CURRENT_DATE) AND e.approvalStatus = 'APPROVED'")
    BigDecimal getYearlyExpense();
    
    // 按类型统计支出
    @Query("SELECT e.type, COALESCE(SUM(e.amount), 0) FROM Expense e WHERE e.approvalStatus = 'APPROVED' GROUP BY e.type")
    List<Object[]> getExpenseByType();
    
    // 按分类统计支出
    @Query("SELECT e.category, COALESCE(SUM(e.amount), 0) FROM Expense e WHERE e.approvalStatus = 'APPROVED' GROUP BY e.category")
    List<Object[]> getExpenseByCategory();
    
    // 按月份统计支出（最近12个月）
    @Query("SELECT YEAR(e.expenseDate), MONTH(e.expenseDate), COALESCE(SUM(e.amount), 0) " +
           "FROM Expense e " +
           "WHERE e.expenseDate >= :startDate AND e.approvalStatus = 'APPROVED' " +
           "GROUP BY YEAR(e.expenseDate), MONTH(e.expenseDate) " +
           "ORDER BY YEAR(e.expenseDate), MONTH(e.expenseDate)")
    List<Object[]> getMonthlyExpenseStats(@Param("startDate") LocalDateTime startDate);
    
    // 获取待审批的支出
    List<Expense> findByApprovalStatusOrderByCreatedAtDesc(Expense.ApprovalStatus status);
    
    // 按日期范围查找支出
    @Query("SELECT e FROM Expense e WHERE e.expenseDate BETWEEN :startDate AND :endDate")
    List<Expense> findByDateRange(String startDate, String endDate);
    
    // 统计待审批支出数量
    @Query("SELECT COUNT(e) FROM Expense e WHERE e.approvalStatus = 'PENDING'")
    Long countPendingExpenses();
}
